﻿using System;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;

using BizElements.Core.ProviderComponents;

namespace BizElements.Core
{
    /// <summary>Common logic for all IEntityDAO implementations in BizBlocks.Core assembly.</summary>
    internal static class DaoUtil
    {
        #region Create filters.

        internal static SearchCondition CreatePrimaryKeyFilter(IEntity entity)
        {
            DbColumnCollection pkFields = entity.Table.PrimaryKey;
            if (pkFields == null || pkFields.Count == 0)
                throw new InvalidOperationException(Messages.EntityDAO_PkNotDefinedForTable);

            object[] pkValues = entity.GetPrimaryKeyValue();
            if (pkValues == null)
                throw new InvalidOperationException(Messages.EntityDAO_PkNotSet);

            SearchCondition pk = new SearchCondition();
            pk.Add(PredicateFactory.Compare(pkFields[0], "=", pkValues[0]));
            for (int compositePkFieldIdx = 1; compositePkFieldIdx < pkFields.Count; compositePkFieldIdx++)
                pk.And(PredicateFactory.Compare(pkFields[compositePkFieldIdx], "=", pkValues[compositePkFieldIdx]));

            return pk;
        }

        internal static SearchCondition CreateFilterFromEntity(IEntity entity)
        {
            SearchCondition filter = new SearchCondition();
            object[] values = entity.ToObjectArray();
            for (int fieldIdx = 0; fieldIdx < values.Length; fieldIdx++)
            {
                if (values[fieldIdx] != null)
                    filter.And(PredicateFactory.Compare(entity.Table.Columns[fieldIdx], "=", values[fieldIdx]));
            }
            return filter;
        }

        internal static SearchCondition CreateFilterMatchingAnyEntityField(IEntity entity)
        {
            SearchCondition filter = new SearchCondition();
            object[] values = entity.ToObjectArray();
            for (int fieldIdx = 0; fieldIdx < values.Length; fieldIdx++)
            {
                if (values[fieldIdx] != null)
                    filter.Or(PredicateFactory.Compare(entity.Table.Columns[fieldIdx], "=", values[fieldIdx]));
            }
            return filter;
        }

        #endregion

        #region Entity data access methods (CRUD). Affect a single row. Suffix "One".

        internal static int DeleteOne(IConnectionProvider conn, IEntity entity, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            SearchCondition pk = CreatePrimaryKeyFilter(entity);
            int numberOfRowsAffected = Delete(conn, entity.Table, pk, out lastExecutedCommandInfo);
            entity.EntityState = EntityState.Deleted;
            return numberOfRowsAffected;
        }

        internal static object InsertOne(IConnectionProvider conn, IEntity entity, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            InsertStatement insert = new InsertStatement(entity.Table);
            int autoIdValueIdx = -1;
            object[] entityValues = entity.ToObjectArray();
            for (int fieldIdx = 0; fieldIdx < entityValues.Length; fieldIdx++)
            {
                IDbColumn currCol = entity.Table.Columns[fieldIdx];
                object currVal = entityValues[fieldIdx];

                if (currCol.AutoIncrement)
                {
                    // Auto-identity field is added to the insert list.
                    // Some DBMSs, such as Oracle, require all fields in the insert list.
                    // Others, such as SQL Server, do not accept auto-identity fields in the list.
                    // InsertStatement class will correctly render the statement and retrieve new ID.
                    insert.AddExpression(currCol, currVal);
                    autoIdValueIdx = fieldIdx;
                }
                else if (currCol.IsAutoGenerated)
                {
                    // Auto-generated field such as timestamp in SQL Server may not be in the insert list.
                    // This type of field is not automatically retrieved after insert.
                }
                else
                {
                    insert.AddExpression(currCol, currVal);
                }
            }

            // Execute statement using this class' connection provider.			
            insert.ConnectionProvider = conn;
            object newId = insert.Execute();
            lastExecutedCommandInfo = insert.LastExecutedCommandInfo;

            // Set new ID.
            if (autoIdValueIdx >= 0)
            {
                if (newId != null)
                {
                    entityValues[autoIdValueIdx] = newId;
                    entity.FromObjectArray(entityValues);

                    // Auto-ID value has been fetched.
                    entity.EntityState = EntityState.Synchronized;
                }
                else
                {
                    // Auto-ID value cannot be fetched.
                    entity.EntityState = EntityState.OutOfSync;
                }
            }
            else
            {
                // Entity doesn't have an auto-increment field.
                entity.EntityState = EntityState.Synchronized;
            }

            return newId;
        }

        internal static bool SelectOne(IConnectionProvider conn, IEntity entity, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            // Return false if primary key is not defined for the entity's table or if the primary key values are not set.
            DbColumnCollection pkFields = entity.Table.PrimaryKey;
            if (pkFields == null || pkFields.Count == 0 || entity.GetPrimaryKeyValue() == null)
            {
                lastExecutedCommandInfo = null;
                return false;
            }

            SearchCondition pk = CreatePrimaryKeyFilter(entity);

            // Fetch data and set members.
            DataTable data = Select(conn, entity.Table, null, pk, -1, null, false, out lastExecutedCommandInfo);
            bool isFetched = (data.Rows.Count > 0);
            if (isFetched)
            {
                entity.FromDataRow(data.Rows[0]);
                entity.EntityState = EntityState.Synchronized;
            }

            return isFetched;
        }

        internal static int UpdateOne(IConnectionProvider conn, IEntity entity, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            SearchCondition pk = CreatePrimaryKeyFilter(entity);

            // Create a set list that changes all columns except those belonging to the primary key,
            // those which are auto-incremented and auto-generated fields such as timestamp or computed fields in SQL Server.
            UpdateList setlist = new UpdateList();
            object[] newValues = entity.ToObjectArray();
            for (int fieldIdx = 0; fieldIdx < entity.Table.Columns.Count; fieldIdx++)
            {
                IDbColumn currCol = entity.Table.Columns[fieldIdx];
                if (!currCol.AutoIncrement && !currCol.IsPrimaryKeyPart && !currCol.IsAutoGenerated)
                    setlist.Add(currCol, newValues[fieldIdx]);
            }

            int numberOfRowsAffected = Update(conn, entity.Table, setlist, pk, out lastExecutedCommandInfo);
            entity.EntityState = EntityState.Synchronized;
            return numberOfRowsAffected;
        }

        internal static bool Save(IConnectionProvider conn, IEntity entity, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            bool isPersisted;
            switch (entity.EntityState)
            {
                case EntityState.New:
                    InsertOne(conn, entity, out lastExecutedCommandInfo);
                    isPersisted = true;
                    break;

                case EntityState.OutOfSync:
                    UpdateOne(conn, entity, out lastExecutedCommandInfo);
                    isPersisted = true;
                    break;

                case EntityState.PendingDeletion:
                    DeleteOne(conn, entity, out lastExecutedCommandInfo);
                    isPersisted = true;
                    break;

                default:
                    isPersisted = false;
                    lastExecutedCommandInfo = null;
                    break;
            }

            return isPersisted;
        }

        #endregion

        #region Select --> DataTable.

        internal static DataTable Select(IConnectionProvider conn, IDbTable table, RelationBucket relations, SearchCondition filter, int maxNumberOfItemsToReturn, OrderByClause sorter, bool sortOnDatabase, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            DataTable result;

            SelectStatement select = new SelectStatement(table);
            select.SelectList.Add(table.Columns);

            if (maxNumberOfItemsToReturn > 0)
                select.Top = maxNumberOfItemsToReturn;

            if (relations != null && relations.Count > 0)
                select.Relations = relations;

            if (filter != null && !filter.IsEmpty)
                select.Where = filter;

            if (sortOnDatabase && (sorter != null) && (sorter.Count > 0))
                select.OrderBy = sorter;

            // Execute statement using this class' connection provider.
            select.ConnectionProvider = conn;
            DataTable retrievedData = select.Execute();
            lastExecutedCommandInfo = select.LastExecutedCommandInfo;

            if (!sortOnDatabase && (sorter != null) && (sorter.Count > 0))
            {
                DataRow[] sortedRows = retrievedData.Select(null, sorter.GetDataViewSortExpression());
                DataTable sortedData = table.NewDataTable();
                foreach (DataRow row in sortedRows)
                    sortedData.ImportRow(row);

                result = sortedData;
            }
            else
            {
                result = retrievedData;
            }

            return result;
        }

        #endregion 

        #region Select --> list.

        internal static void Select(IList destination, IObjectReader entityFiller, IConnectionProvider conn, IDbTable table, RelationBucket relations, SearchCondition filter, int maxNumberOfItemsToReturn, OrderByClause sorter, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            SelectStatement select = new SelectStatement(table);
            select.SelectList.Add(table.Columns);

            if (maxNumberOfItemsToReturn > 0)
                select.Top = maxNumberOfItemsToReturn;

            if (relations != null && relations.Count > 0)
                select.Relations = relations;

            if (filter != null && !filter.IsEmpty)
                select.Where = filter;

            if (sorter != null && sorter.Count > 0)
                select.OrderBy = sorter;

            // Execute statement using this class' connection provider.
            select.ConnectionProvider = conn;
            select.FillCollection(destination, entityFiller);
            lastExecutedCommandInfo = select.LastExecutedCommandInfo;
        }

        #endregion

        #region Update.

        internal static int Update(IConnectionProvider conn, IDbTable table, UpdateList setExpressions, SearchCondition filter, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            if (filter == null || filter.IsEmpty)
                throw new ArgumentException(Messages.EntityDAO_SearchConditionMayNotBeNullOrEmpty, "filter");

            if (setExpressions == null || setExpressions.Count == 0)
                throw new ArgumentException(Messages.EntityDAO_UpdateListMayNotBeNullOrEmpty, "setExpressions");

            UpdateStatement update = new UpdateStatement(table);
            update.UpdateList = setExpressions;
            update.Where = filter;

            // Execute statement using this class' connection provider.			
            update.ConnectionProvider = conn;
            int rowsAffected = update.Execute();
            lastExecutedCommandInfo = update.LastExecutedCommandInfo;
            return rowsAffected;
        }

        #endregion

        #region Delete.

        internal static int Delete(IConnectionProvider conn, IDbTable table, SearchCondition filter, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            if (filter == null || filter.IsEmpty)
                throw new ArgumentException(Messages.EntityDAO_SearchConditionMayNotBeNullOrEmpty, "filter");

            DeleteStatement delete = new DeleteStatement(table);
            delete.Where = filter;

            // Execute statement using this class' connection provider.
            delete.ConnectionProvider = conn;
            int numberOfRowsAffected = delete.Execute();
            lastExecutedCommandInfo = delete.LastExecutedCommandInfo;
            return numberOfRowsAffected;
        }

        #endregion

        #region Aggregate functions.

        internal static int Count(IConnectionProvider conn, IDbTable table, RelationBucket relations, SearchCondition filter, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            SelectStatement select = new SelectStatement(table);
            select.SelectList.Add(AggregateFunctionFactory.Count("numberOfRows"));
            if (relations != null && relations.Count > 0)
                select.Relations = relations;
            if (filter != null && !filter.IsEmpty)
                select.Where = filter;

            // Prepare or execute command.
            select.ConnectionProvider = conn;
            DataTable numberOfRowsData = select.Execute();
            lastExecutedCommandInfo = select.LastExecutedCommandInfo;
            int count = Convert.ToInt32(numberOfRowsData.Rows[0]["numberOfRows"], CultureInfo.InvariantCulture);
            return count;
        }

        internal static object FetchAggregateValue(IConnectionProvider conn, IDbTable table, AggregateFunction function, RelationBucket relations, SearchCondition filter, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            SelectStatement select = new SelectStatement(table);
            select.SelectList.Add(function);
            if (relations != null && relations.Count > 0)
                select.Relations = relations;
            if (filter != null && !filter.IsEmpty)
                select.Where = filter;

            // Execute statement using this class' connection provider.
            select.ConnectionProvider = conn;
            DataTable retrievedData = select.Execute();
            lastExecutedCommandInfo = select.LastExecutedCommandInfo;
            object aggregateValue = retrievedData.Rows[0][0];
            return aggregateValue;
        }

        #endregion

        #region FetchParent(s)

        internal static bool SelectOne(IConnectionProvider conn, IEntity entity, DbRelation[] parentsToFetch, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            if (parentsToFetch.Length == 0)
                return SelectOne(conn, entity, out lastExecutedCommandInfo);

            bool connIsLocal = !conn.IsOpen;
            if (connIsLocal)
                conn.OpenConnection();

            bool isFetched = false;
            try
            {
                isFetched = SelectOne(conn, entity, out lastExecutedCommandInfo);
                if (isFetched)
                    FetchParents(conn, entity, parentsToFetch, out lastExecutedCommandInfo);
            }
            finally
            {
                if (connIsLocal)
                    conn.CloseConnection();
            }

            return isFetched;
        }

        internal static IEntity FetchParent(IConnectionProvider conn, IEntity entity, DbRelation foreignKey, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            IEntity parent = null;
            lastExecutedCommandInfo = null;

            // Current entity's table must be defined as a child table in the given FK.
            //throw new InvalidOperationException("Current entity's table must be defined as a child table in the given FK. Expected " + this.Entity.Table.Alias + ", but " + foreignKey.Child.Alias + " was provided.");
            if (!foreignKey.Child.HasEqualAliasAndNameAs(entity.Table))
                throw new ArgumentException(string.Format(CultureInfo.InvariantCulture, Messages.EntityBase_CurrTableMustBeParentExpectedXButGotY, entity.Table.Alias, foreignKey.Child.Alias), "foreignKey");

            // Prepare FK values.
            bool fkIsSet = true;
            object[] fkValues = new object[foreignKey.ChildForeignKey.Length];
            for (int fkFieldIdx = 0; fkFieldIdx < fkValues.Length; fkFieldIdx++)
            {
                fkValues[fkFieldIdx] = entity.GetField(foreignKey.ChildForeignKey[fkFieldIdx]);
                if (fkValues[fkFieldIdx] == null)
                    fkIsSet = false;
            }

            if (fkIsSet)
            {
                // Fetch parent entity. Return NULL if a parent with the given key values doesn't exist.
                parent = foreignKey.Parent.NewEntity();
                for (int parentPkFieldIdx = 0; parentPkFieldIdx < foreignKey.ParentPrimaryKey.Length; parentPkFieldIdx++)
                    parent.SetField(foreignKey.ParentPrimaryKey[parentPkFieldIdx], fkValues[parentPkFieldIdx]);
                
                IEntityDAO parentDao = EntityDaoFactory.GetEntityDAO(parent, conn);
                bool isFetched = parentDao.SelectOne();
                lastExecutedCommandInfo = parentDao.LastExecutedCommandInfo;
                if (isFetched)
                {
                    entity.SetParent(foreignKey, parent);
                }
            }

            return parent;
        }

        internal static IEntity[] FetchParents(IConnectionProvider conn, IEntity entity, DbRelation[] parentsToFetch, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            lastExecutedCommandInfo = null;
            IEntity[] parents = new IEntity[parentsToFetch.Length];
            if (parents.Length > 0)
            {
                bool connIsLocal = !conn.IsOpen;
                if (connIsLocal)
                    conn.OpenConnection();

                // All parents are fetched using the same open connection.
                try
                {
                    for (int parentIdx = 0; parentIdx < parents.Length; parentIdx++)
                        parents[parentIdx] = FetchParent(conn, entity, parentsToFetch[parentIdx], out lastExecutedCommandInfo);
                }
                finally
                {
                    if (connIsLocal)
                        conn.CloseConnection();
                }
            }

            return parents;
        }

        #endregion

        #region Single-trip SelectOneWithParents.

        internal static bool SelectOneWithParents(IConnectionProvider conn, IEntity entity, DbRelation[] parentsToFetch, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            EnsureCurrentEntityTableIsDefinedAsChildTableInGivenFKs(entity, parentsToFetch);
            EnsurePkIsDefined(entity);
            // Clone relations because they will be modified - column aliases for parent tables will be set. Original relation are to be left unmodified.
            DbRelation[] relations = CopyRelations(parentsToFetch, /*ensure unique parent table aliases*/ true);
            DataRow row = FetchEntityAndParentData(conn, entity, relations, out lastExecutedCommandInfo);
            if (row == null)
                return false;

            if (entity.Table.ColumnAliasesArePrefixed)
                entity.FromDataRow(row, entity.Table);
            else
                entity.FromDataRow(row);

            foreach (DbRelation fk in relations)
            {
                if (IsFkSet(entity, fk))
                {
                    IEntity parent = fk.Parent.NewEntity();
                    parent.FromDataRow(row, fk.Parent);
                    entity.SetParent(fk, parent);
                }
            }

            return true;
        }

        private static bool IsFkSet(IEntity entity, DbRelation fk)
        {
            foreach (IDbColumn fkPart in fk.ChildForeignKey)
            {
                if (entity.IsNull(fkPart))
                    return false;
            }

            return true;
        }

        private static DbRelation[] CopyRelations(DbRelation[] parentsToFetch, bool ensureUniqueParentTableAliases)
        {
            DbRelation[] relations = new DbRelation[parentsToFetch.Length];
            for (int idxRel = 0; idxRel < relations.Length; idxRel++)
            {
                if (ensureUniqueParentTableAliases)
                {
                    // Ensure parent tables have unique aliases.
                    IDbTable parentTable = parentsToFetch[idxRel].Parent;
                    bool shouldCreateUniqueAlias = ContainsMultipleRelationsToParentTable(parentsToFetch, parentTable.TableName);
                    string uniqueParentAlias = (shouldCreateUniqueAlias) ? string.Format(CultureInfo.InvariantCulture, "{0}_{1}", parentTable.TableName, idxRel) : null;
                    relations[idxRel] = parentsToFetch[idxRel].Clone(null, /*child cols wo aliases*/ false, uniqueParentAlias, /*parent cols with aliases*/ true);
                }
                else
                {
                    relations[idxRel] = parentsToFetch[idxRel].Clone(/*child cols wo aliases*/ false, /*parent cols with aliases*/ true);
                }
            }

            return relations;
        }

        private static bool ContainsMultipleRelationsToParentTable(DbRelation[] relations, string parentTableName)
        {
            int count = 0;
            foreach (DbRelation rel in relations)
            {
                if (rel.Parent.TableName == parentTableName)
                    count++;
            }

            if (count > 1)
                return true;
            else
                return false;
        }

        private static DataRow FetchEntityAndParentData(IConnectionProvider conn, IEntity entity, DbRelation[] relations, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            SelectStatement select = new SelectStatement(entity.Table, entity.Table.Columns);
            foreach (DbRelation fk in relations)
            {
                // Prefixed aliases for parent columns are alread set in CopyRelations method.
                // SetPrefixedAliases() usage must be avoided. It will be declared as obsolete and removed in a future release.
                //fk.Parent.SetPrefixedAliases();
                select.SelectList.Add(fk.Parent.Columns);
                select.Relations.Add(fk);
            }
            foreach (IDbColumn pkPart in entity.Table.PrimaryKey)
                select.Where.And(pkPart, entity.GetField(pkPart));

            DataTable data = select.Execute(conn);
            lastExecutedCommandInfo = select.LastExecutedCommandInfo;
            return (data.Rows.Count > 0) ? data.Rows[0] : null;
        }

        private static bool IsPkDefined(IEntity entity)
        {
            DbColumnCollection pkFields = entity.Table.PrimaryKey;
            if (pkFields == null || pkFields.Count == 0 || entity.GetPrimaryKeyValue() == null)
                return false;
            else
                return true;
        }

        private static void EnsurePkIsDefined(IEntity entity)
        {
            if (!IsPkDefined(entity))
                throw new InvalidOperationException(Messages.EntityDAO_PkMustBeDefinedAndInited);
        }

        private static void EnsureCurrentEntityTableIsDefinedAsChildTableInGivenFKs(IEntity entity, DbRelation[] foreignKeys)
        {
            foreach (DbRelation foreignKey in foreignKeys)
            {
                //throw new InvalidOperationException("Current entity's table must be defined as a child table in the given FK. Expected " + entity.Table.Alias + ", but " + foreignKey.Child.Alias + " was provided.");
                if (!foreignKey.Child.HasEqualAliasAndNameAs(entity.Table))
                    throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, Messages.EntityBase_CurrTableMustBeParentExpectedXButGotY, entity.Table.Alias, foreignKey.Child.Alias));
            }
        }

        #endregion

        #region Single-trip SelectOneWithPartialParents.

        internal static bool SelectOneWithPartialParents(IConnectionProvider conn, IEntity entity, IEnumerable<DbRelation> foreignKeys, IEnumerable<IDbColumn> parentColumnsToFetch, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            List<ParentScope> parentRelationsAndFields = CreateAlisedParentRelationsAndFields(foreignKeys, parentColumnsToFetch);
            SelectStatement select = CreateSelectOneWithPartialParentsStatement(entity, parentRelationsAndFields);
            DataTable data = select.Execute(conn);
            lastExecutedCommandInfo = select.LastExecutedCommandInfo;
            if (data.Rows.Count == 0)
                return false;

            DataRow row = data.Rows[0];
            entity.FromDataRow(row, entity.Table);
            foreach (ParentScope parentScope in parentRelationsAndFields)
            {
                IEntity parent = TryCreatePartialParent(row, parentScope);
                entity.SetParent(parentScope.Relation, parent);
            }

            entity.EntityState = EntityState.Synchronized;
            return true;
        }

        private static IEntity TryCreatePartialParent(DataRow row, ParentScope parentScope)
        {
            // Child columns don't have aliases. Parens columns have aliases.
            DbRelation fk = parentScope.Relation;
            IEntity parent = parentScope.Relation.Parent.NewEntity();
            for (int i = 0; i < fk.ChildForeignKey.Length; i++)
            {
                IDbColumn childFkPart = fk.ChildForeignKey[i];
                object keyPartValue = TryGetColumnValue(row, childFkPart.ColumnName);
                if (keyPartValue == null)
                    return null;

                IDbColumn parentPkPart = fk.ParentPrimaryKey[i];
                parent.SetField(parentPkPart, keyPartValue);
            }

            foreach (IDbColumn field in parentScope.ParentFields)
            {
                object fieldValue = TryGetColumnValue(row, field.Alias);
                parent.SetField(field, fieldValue);
            }

            return parent;
        }

        private static bool IsFkSet(DataRow row, DbRelation fk)
        {
            foreach (IDbColumn fkPart in fk.ChildForeignKey)
            {
                object partValue = TryGetColumnValue(row, fkPart.Alias);
                if (partValue == null)
                    return false;
            }

            return true;
        }

        private static object TryGetColumnValue(DataRow row, string columnName)
        {
            int colIdx = row.Table.Columns.IndexOf(columnName);
            object val;
            if (colIdx >= 0)
            {
                val = row[colIdx];
                if (val == DBNull.Value)
                    val = null;
            }
            else
            {
                val = null;
            }

            return val;
        }

        private static List<ParentScope> CreateAlisedParentRelationsAndFields(IEnumerable<DbRelation> foreignKeys, IEnumerable<IDbColumn> parentColumnsToFetch)
        {
            List<ParentScope> parentRelationsAndFields = new List<ParentScope>();
            int fkOrdinal = 1;
            foreach (DbRelation fk in foreignKeys)
            {
                List<string> parentColumnNames = parentColumnsToFetch
                    .Where(col => (col.Table.TableName == fk.Parent.TableName))
                    .ToList()
                    .ConvertAll(col => col.ColumnName);

                string parentAlias = string.Concat(fk.Parent.TableName, "_", fkOrdinal);
                DbRelation aliasedRelation = fk.Clone(null, false, parentAlias, true);
                parentRelationsAndFields.Add(new ParentScope(aliasedRelation, parentColumnNames));
                fkOrdinal++;
            }
            return parentRelationsAndFields;
        }

        private static SelectStatement CreateSelectOneWithPartialParentsStatement(IEntity entity, List<ParentScope> parentRelationsAndFields)
        {
            IDbTable tbl = entity.Table;
            SelectStatement select = new SelectStatement(tbl, tbl.Columns);
            foreach (ParentScope relationAndFields in parentRelationsAndFields)
            {
                select.Relations.Add(relationAndFields.Relation);
                foreach (IDbColumn aliasedParentCol in relationAndFields.ParentFields)
                    select.SelectList.Add(aliasedParentCol);
            }

            return select;
        }

        #endregion

        #region 1:N children.

        internal static void FetchChildren(IList destination, IConnectionProvider conn, IEntity entity, DbRelation relation, SearchCondition childRowsFilter, OrderByClause orderBy, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            if (entity.GetPrimaryKeyValue() == null)
                throw new InvalidOperationException(Messages.EntityDAO_PkOfCurrParentEntityIsNotSet);

            // Only tablenames are compared, aliases don't have to be equal.
            if (relation.Parent.TableName != entity.Table.TableName)
                throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, Messages.EntityDAO_CurrEntityTableMustBeParentExpectedXGotY, entity.Table.TableName, relation.Parent.TableName));

            // Set child FK fields.
            IEntityDAO childDao = EntityDaoFactory.GetEntityDAO(relation.Child.NewEntity(), conn);
            SearchCondition chilkFkAndFilter = new SearchCondition();
            for (int fieldIdxInRelation = 0; fieldIdxInRelation < relation.ChildForeignKey.Length; fieldIdxInRelation++)
                chilkFkAndFilter.And(PredicateFactory.Compare(relation.ChildForeignKey[fieldIdxInRelation], ComparisonOperator.Equal, entity.GetField(relation.ParentPrimaryKey[fieldIdxInRelation])));

            if (!SearchCondition.IsNullOrEmpty(childRowsFilter))
                chilkFkAndFilter.And(childRowsFilter);

            childDao.SetSorter(orderBy);
            childDao.Select(destination, null, chilkFkAndFilter, -1);
            lastExecutedCommandInfo = childDao.LastExecutedCommandInfo;
        }

        internal static DataTable FetchChildren(IConnectionProvider conn, IEntity entity, DbRelation relation, SearchCondition childRowsFilter, OrderByClause orderBy, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            if (entity.GetPrimaryKeyValue() == null)
                throw new InvalidOperationException(Messages.EntityDAO_PkOfCurrParentEntityIsNotSet);

            // Only tablenames are compared, aliases don't have to be equal.
            if (relation.Parent.TableName != entity.Table.TableName)
                throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, Messages.EntityDAO_CurrEntityTableMustBeParentExpectedXGotY, entity.Table.TableName, relation.Parent.TableName));

            // Set child FK fields.
            IEntityDAO childDao = EntityDaoFactory.GetEntityDAO(relation.Child.NewEntity(), conn);
            SearchCondition chilkFkAndFilter = new SearchCondition();
            for (int fieldIdxInRelation = 0; fieldIdxInRelation < relation.ChildForeignKey.Length; fieldIdxInRelation++)
                chilkFkAndFilter.And(PredicateFactory.Compare(relation.ChildForeignKey[fieldIdxInRelation], ComparisonOperator.Equal, entity.GetField(relation.ParentPrimaryKey[fieldIdxInRelation])));

            if (!SearchCondition.IsNullOrEmpty(childRowsFilter))
                chilkFkAndFilter.And(childRowsFilter);

            childDao.SetSorter(orderBy);
            DataTable children = childDao.Select(null, chilkFkAndFilter, -1);
            lastExecutedCommandInfo = childDao.LastExecutedCommandInfo;

            return children;
        }

        #endregion

        #region M:N children.

        internal static void CreateChildToJunctionRelationAndFilter(IEntity entity, ManyToManyRelation mnRelation, PrimaryTableIndex parent, SearchCondition childFilter, out IDbTable childTable, out SearchCondition junctionFilter, out DbRelation aliasFreeRelation)
        {
            if (entity.GetPrimaryKeyValue() == null)
                throw new InvalidOperationException(Messages.EntityDAO_PkOfCurrParentEntityIsNotSet);

            IDbTable parentTable;
            DbRelation parentToJunctionRelation;
            DbRelation childToJunctionRelation;

            switch (parent)
            {
                case PrimaryTableIndex.FirstPrimaryTable:
                    // Current entity (parent) is 1st primary table in MN-relation.
                    parentTable = mnRelation.FirstPrimaryTable;
                    parentToJunctionRelation = mnRelation.FirstTableToJunction;
                    childTable = mnRelation.SecondPrimaryTable;
                    childToJunctionRelation = mnRelation.SecondTableToJunction;
                    break;
                case PrimaryTableIndex.SecondPrimaryTable:
                    // Current entity (parent) is 2nd primary table in MN-relation.
                    parentTable = mnRelation.SecondPrimaryTable;
                    parentToJunctionRelation = mnRelation.SecondTableToJunction;
                    childTable = mnRelation.FirstPrimaryTable;
                    childToJunctionRelation = mnRelation.FirstTableToJunction;
                    break;
                default:
                    throw new ArgumentException(Messages.EntityDAO_UnsupportedManyToManyParentValue + parent.ToString(), "parent");
            }

            //throw new InvalidOperationException("Invalid parent table. Expected " + this.Entity.Table.TableName + ", but " + parentTable.TableName + " was provided.");
            if (parentTable.TableName != entity.Table.TableName)
                throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, Messages.EntityDAO_InvalidParentTableExpectedXGotY, entity.Table.TableName, parentTable.TableName));

            // Junction parent FK must be equal to this entity's PK. Additional filter for junction rows may be specified.
            junctionFilter = new SearchCondition();
            for (int parentToJunctionFieldIdxInRelation = 0; parentToJunctionFieldIdxInRelation < parentToJunctionRelation.ChildForeignKey.Length; parentToJunctionFieldIdxInRelation++)
            {
                junctionFilter.And(PredicateFactory.Compare(
                    parentToJunctionRelation.ChildForeignKey[parentToJunctionFieldIdxInRelation], "=",
                    entity.GetField(parentToJunctionRelation.ParentPrimaryKey[parentToJunctionFieldIdxInRelation])));
            }

            if (mnRelation.JunctionRowsFilter != null && !mnRelation.JunctionRowsFilter.IsEmpty)
                junctionFilter.And(mnRelation.JunctionRowsFilter);

            // Add child rows filter.
            if (childFilter != null && !childFilter.IsEmpty)
                junctionFilter.And(childFilter);

            // M:N usage scenarios:
            //
            // Real relation - when developer model m-n relations in databases using 3 tables, i.e. two primary and one junction tables. In these cases 
            // we can safely create a relation between junction and child table that doesn't use aliases for child table, ie. the parent in the following 
            // query/relation. Aliases are not required for this simple query.  his way RelationBucket will never have a problem connecting these two tables.
            //
            // Hack used in by FetchChildrenWithParents method. This is not a real M:N relations but the effect is the same. However, it is possible
            // that another child's parents belong to the same table as child themselves. E.g. City -> Employee -> Manager. In these cases we need aliases.
            bool isHackForFetchChildrenWithParentsMethod = (childToJunctionRelation.Child.TableName == childToJunctionRelation.Parent.TableName);
            if (isHackForFetchChildrenWithParentsMethod)
            {
                IDbTable child = childToJunctionRelation.Parent;
                // Bad name --> in this case we actualy keep aliases if they were previously set.
                aliasFreeRelation = new DbRelation(child, child.PrimaryKey.ToArray(), childToJunctionRelation.Child, childToJunctionRelation.ChildForeignKey, null);
            }
            else
            {
                IDbTable child = childToJunctionRelation.Parent.Clone(null);
                aliasFreeRelation = new DbRelation(child, child.PrimaryKey.ToArray(), childToJunctionRelation.Child, childToJunctionRelation.ChildForeignKey, null);
            }            
        }

        #endregion

        #region FetchParentsForCollection.

        internal static void FetchParentsForCollection(IEnumerable childEntities, DbRelation parentsToFetch, bool shareParentObjects, IConnectionProvider conn, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            // Modes: Foreach, IN (simple FK), OR-AND (composite FK).
            lastExecutedCommandInfo = null;

            // Check if it makes sense to proceed.
            if (ArrayUtil.IsNullOrEmpty(childEntities))
                return;

            // Collection of 'empty' parents. Only PKs are set. No duplicate PK value in unique parents collection.
            IList uniqueParents = new List<IEntity>();
            foreach (IEntity kid in childEntities)
            {
                object[] pkValue = PkUtil.CreatePkFromFk(kid, parentsToFetch);
                if (pkValue == null)
                    continue;

                IEntity parent = parentsToFetch.Parent.NewEntity();
                for (int pkPart = 0; pkPart < pkValue.Length; pkPart++)
                    parent.SetField(parent.Table.PrimaryKey[pkPart], pkValue[pkPart]);

                if (uniqueParents.IndexOf(pkValue) < 0)
                    uniqueParents.Add(parent);
            }

            // Check if it makes sense to proceed.
            if (uniqueParents.Count == 0)
                return;

            // Determine which algorithm to use.
            const int approxRecordCountWhereOptimizationsMakeSense = 3;
            const int approxMaxNumberOfParametersAllowedInSelectStatement = 800;
            int requiredNumberOfDbParameters = uniqueParents.Count * parentsToFetch.ParentPrimaryKey.Length;
            bool useOptimizations = (uniqueParents.Count > approxRecordCountWhereOptimizationsMakeSense)
                && (requiredNumberOfDbParameters < approxMaxNumberOfParametersAllowedInSelectStatement);

            bool isCompositePk = (parentsToFetch.ParentPrimaryKey.Length > 1);
            bool useWhereIn = (useOptimizations && !isCompositePk);
            bool useWhereOrAnd = (useOptimizations && isCompositePk);

            IDbTable parentTable = parentsToFetch.Parent;
            if (useWhereIn)
                FetchParentsForCollectionUsingWhereInAlgorithm(uniqueParents, parentTable, conn, out lastExecutedCommandInfo);
            else if (useWhereOrAnd)
                FetchParentsForCollectionUsingWhereOrAndAlgorithmForCompositePK(uniqueParents, parentTable, conn, out lastExecutedCommandInfo);
            else
                FetchParentsForCollectionUsingMultipleRoundtrips(uniqueParents, parentTable, conn, out lastExecutedCommandInfo);

            // Collection is now guaranteed to be filled with data.
            childEntities.SetParents(parentsToFetch, uniqueParents, shareParentObjects);
        }

        private static void FetchParentsForCollectionUsingWhereInAlgorithm(IList parentsWithPK, IDbTable parentTable, IConnectionProvider conn, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            // WHERE id in (@id1, @id2, @id3...)
            object[] parentIds = new object[parentsWithPK.Count];
            for (int idxParent = 0; idxParent < parentIds.Length; idxParent++)
                parentIds[idxParent] = ((IEntity)parentsWithPK[idxParent]).GetPrimaryKeyValue()[0];

            // Ensure no aliases are used because generated NewEntity() methods don't propagate aliases.
            IDbColumn pkField = parentTable.PrimaryKey[0];
            SearchCondition wherePkIn = new SearchCondition(PredicateFactory.In(pkField, parentIds));

            IEntityDAO daoParents = EntityDaoFactory.GetEntityDAO(parentTable.NewEntity(), conn);
            daoParents.Select(parentsWithPK, null, wherePkIn);
            lastExecutedCommandInfo = daoParents.LastExecutedCommandInfo;
        }

        private static void FetchParentsForCollectionUsingWhereOrAndAlgorithmForCompositePK(IList parentsWithPK, IDbTable parentTable, IConnectionProvider conn, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            // WHERE (pkOne = @pkOne1  AND  pkTwo = @pkTwo1) OR (pkOne = @pkOne2  AND  pkTwo = @pkTwo2) OR ...
            object[][] parentPks = new object[parentsWithPK.Count][];
            for (int idxParent = 0; idxParent < parentPks.Length; idxParent++)
                parentPks[idxParent] = ((IEntity)parentsWithPK[idxParent]).GetPrimaryKeyValue();

            // Ensure no aliases are used because generated NewEntity() methods don't propagate aliases.
            DbColumnCollection pkFields = parentTable.PrimaryKey;
            SearchCondition firstOrSecondOrEtc = new SearchCondition();
            foreach (object[] pkValue in parentPks)
            {
                SearchCondition currParentFilter = new SearchCondition();
                for (int pkPart = 0; pkPart < pkValue.Length; pkPart++)
                    currParentFilter.And(PredicateFactory.Compare(pkFields[pkPart], ComparisonOperator.Equal, pkValue[pkPart]));

                firstOrSecondOrEtc.Or(currParentFilter);
            }

            IEntityDAO daoParents = EntityDaoFactory.GetEntityDAO(parentTable.NewEntity(), conn);
            daoParents.Select(parentsWithPK, null, firstOrSecondOrEtc);
            lastExecutedCommandInfo = daoParents.LastExecutedCommandInfo;
        }

        private static void FetchParentsForCollectionUsingMultipleRoundtrips(IList parentsWithPK, IDbTable parentTable, IConnectionProvider conn, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            // foreach... SelectOne...            
            bool connIsLocal = !conn.IsOpen;
            if (connIsLocal)
                conn.OpenConnection();

            lastExecutedCommandInfo = null;
            try
            {
                foreach (IEntity parent in parentsWithPK)
                {
                    IEntityDAO daoParent = EntityDaoFactory.GetEntityDAO(parent, conn);
                    daoParent.SelectOne();
                    lastExecutedCommandInfo = daoParent.LastExecutedCommandInfo;
                }
            }
            finally
            {
                if (connIsLocal)
                    conn.CloseConnection();
            }
        }

        #endregion

        #region FetchChildrenForCollection.

        internal static TChildrenCollection[] FetchChildrenForCollection<TChildrenCollection>(IEnumerable parentEntities, DbRelation relation, IConnectionProvider conn)
            where TChildrenCollection : IList, new()
        {
            // Init empty collections now, since parents without PK are skipped in "algorithm methods".
            int parentEntitiesCount = ArrayUtil.Count(parentEntities);
            TChildrenCollection[] childCollectionsInSameOrderAsParents = new TChildrenCollection[parentEntitiesCount];
            for (int idxChildCollection = 0; idxChildCollection < childCollectionsInSameOrderAsParents.Length; idxChildCollection++)
                childCollectionsInSameOrderAsParents[idxChildCollection] = new TChildrenCollection();

            // Check if it makes sense to proceed.
            if (parentEntitiesCount == 0)
                return childCollectionsInSameOrderAsParents;

            // Modes: Foreach, IN (simple FK), OR-AND (composite FK). 
            // Determine which algorithm to use.
            const int approxRecordCountWhereOptimizationsMakeSense = 3;
            const int approxMaxNumberOfParametersAllowedInSelectStatement = 800;
            int requiredNumberOfDbParameters = parentEntitiesCount * relation.ParentPrimaryKey.Length;
            bool useOptimizations = (parentEntitiesCount > approxRecordCountWhereOptimizationsMakeSense)
                && (requiredNumberOfDbParameters < approxMaxNumberOfParametersAllowedInSelectStatement);

            bool isCompositeFk = (relation.ChildForeignKey.Length > 1);
            bool useWhereIn = (useOptimizations && !isCompositeFk);
            bool useWhereOrAnd = (useOptimizations && isCompositeFk);

            if (useWhereIn)
                FetchChildrenForCollectionUsingWhereInAlgorithm(parentEntities, relation, childCollectionsInSameOrderAsParents, conn);
            else if (useWhereOrAnd)
                FetchChildrenForCollectionUsingWhereOrAndAlgorithmForCompositeFK(parentEntities, relation, childCollectionsInSameOrderAsParents, conn);
            else
                FetchChildrenForCollectionUsingMultipleRoundtrips(parentEntities, relation, childCollectionsInSameOrderAsParents, conn);

            // Set parents to child entities.
            int childCollectionIndex = 0;
            foreach (IEntity parent in parentEntities)
            {
                if (childCollectionsInSameOrderAsParents[childCollectionIndex].Count > 0)
                    childCollectionsInSameOrderAsParents[childCollectionIndex].SetParents(relation, parent);

                childCollectionIndex++;
            }

            return childCollectionsInSameOrderAsParents;
        }

        private static void FetchChildrenForCollectionUsingWhereInAlgorithm<TChildrenCollection>(IEnumerable parentEntities, DbRelation relation, TChildrenCollection[] childCollectionsInSameOrderAsParents, IConnectionProvider conn)
            where TChildrenCollection : IList, new()
        {
            ArrayList parentIds = new ArrayList();
            Hashtable keyToIndexMap = new Hashtable();
            int idxParent = 0;
            foreach (IEntity parent in parentEntities)
            {
                object[] pkValue = parent.GetPrimaryKeyValue();
                if (pkValue != null)
                {
                    parentIds.Add(pkValue[0]);
                    keyToIndexMap.Add(pkValue[0], idxParent);
                }

                idxParent++;
            }

            TChildrenCollection allChildren = new TChildrenCollection();
            IEntityDAO daoChildren = EntityDaoFactory.GetEntityDAO(relation.Child.NewEntity(), conn);
            SearchCondition whereFkIn = new SearchCondition(PredicateFactory.In(relation.ChildForeignKey[0], parentIds.ToArray()));
            daoChildren.Select(allChildren, null, whereFkIn);

            // Split children by parents.
            IDbColumn fkField = relation.ChildForeignKey[0];
            foreach (IEntity kid in allChildren)
            {
                object fkval = kid.GetField(fkField);
                int parentIdx = (int)keyToIndexMap[fkval];
                childCollectionsInSameOrderAsParents[parentIdx].Add(kid);
            }
        }

        private static void FetchChildrenForCollectionUsingWhereOrAndAlgorithmForCompositeFK<TChildrenCollection>(IEnumerable parentEntities, DbRelation relation, TChildrenCollection[] childCollectionsInSameOrderAsParents, IConnectionProvider conn)
            where TChildrenCollection : IList, new()
        {
            // WHERE (fkOne = @fkOne1  AND  fkTwo = @fkTwo1) OR (fkOne = @fkOne2  AND  fkTwo = @fkTwo2) OR ...
            SearchCondition firstOrSecondOrEtc = new SearchCondition();
            foreach (IEntity parent in parentEntities)
            {
                if (parent.GetPrimaryKeyValue() != null)
                {
                    SearchCondition currParentFilter = new SearchCondition();
                    for (int fieldIdxInRelation = 0; fieldIdxInRelation < relation.ChildForeignKey.Length; fieldIdxInRelation++)
                        currParentFilter.And(PredicateFactory.Compare(relation.ChildForeignKey[fieldIdxInRelation], ComparisonOperator.Equal, parent.GetField(relation.ParentPrimaryKey[fieldIdxInRelation])));

                    firstOrSecondOrEtc.Or(currParentFilter);
                }
            }

            TChildrenCollection allChildren = new TChildrenCollection();
            IEntityDAO daoChildren = EntityDaoFactory.GetEntityDAO(relation.Child.NewEntity(), conn);
            daoChildren.Select(allChildren, null, firstOrSecondOrEtc);

            // Split children by parents.
            Hashtable keyToIndexMap = new Hashtable();
            int idxParent = 0;
            foreach (IEntity parent in parentEntities)
            {
                bool isPkSet = (null != parent.GetPrimaryKeyValue());
                if (isPkSet)
                {
                    // Key parts must be in same order as in relation. 
                    // GetPrimaryKeyValue method may return values in different order.
                    IDbColumn[] pkFields = relation.ParentPrimaryKey;
                    object[] compositeKeyValue = new object[pkFields.Length];
                    for (int keyPart = 0; keyPart < pkFields.Length; keyPart++)
                        compositeKeyValue[keyPart] = parent.GetField(pkFields[keyPart]);

                    string delimitedKey = CreateHashtableKey(compositeKeyValue);
                    keyToIndexMap.Add(delimitedKey, idxParent);
                }

                idxParent++;
            }

            IDbColumn[] fkFields = relation.ChildForeignKey;
            foreach (IEntity kid in allChildren)
            {
                object[] compositeFkValue = new object[fkFields.Length];
                for (int fkPart = 0; fkPart < fkFields.Length; fkPart++)
                    compositeFkValue[fkPart] = kid.GetField(fkFields[fkPart]);

                string delimitedKey = CreateHashtableKey(compositeFkValue);
                int parentIdx = (int)keyToIndexMap[delimitedKey];
                childCollectionsInSameOrderAsParents[parentIdx].Add(kid);
            }
        }

        private static string CreateHashtableKey(object[] compositeKey)
        {
            string delimitedKey = "";
            foreach (object keyPart in compositeKey)
                delimitedKey += keyPart.ToString().Replace("#", @"\#") + "#";

            return delimitedKey;
        }

        private static void FetchChildrenForCollectionUsingMultipleRoundtrips<TChildrenCollection>(IEnumerable parentEntities, DbRelation relation, TChildrenCollection[] childCollectionsInSameOrderAsParents, IConnectionProvider conn)
            where TChildrenCollection : IList, new()
        {
            // foreach... FetchChildren...
            bool connIsLocal = !conn.IsOpen;
            if (connIsLocal)
                conn.OpenConnection();

            try
            {
                int childCollectionIndex = 0;
                foreach (IEntity parent in parentEntities)
                {
                    // Skip parents (new entities) without PK.
                    if (parent.GetPrimaryKeyValue() != null)
                    {
                        IEntityDAO daoParent = EntityDaoFactory.GetEntityDAO(parent, conn);
                        daoParent.FetchChildren(childCollectionsInSameOrderAsParents[childCollectionIndex], relation, null);
                    }

                    childCollectionIndex++;                    
                }
            }
            finally
            {
                if (connIsLocal)
                    conn.CloseConnection();
            }
        }

        #endregion
    }

    internal sealed class ParentScope
    {
        public ParentScope(DbRelation relation, IEnumerable<string> parentColumnNames)
        {
            DbColumnCollection allColumns = relation.Parent.Columns;
            List<IDbColumn> specifiedColumns = new List<IDbColumn>();
            foreach (string colName in parentColumnNames)
                specifiedColumns.Add(allColumns.GetByColumnName(colName));

            this.Relation = relation;
            this.ParentFields = specifiedColumns;
        }

        public DbRelation Relation { get; private set; }
        public IEnumerable<IDbColumn> ParentFields { get; private set; }
    }
}
